﻿using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using TrafficArchives.Common;

namespace TrafficArchives.DataAccess
{
    /// <summary>
    /// 用户数据库操作类。
    /// </summary>
    public class UserDb : DbBase
    {
        /// <summary>
        /// 取用户数据。
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public override PaginationData<DataTable> GetData(Request request)
        {
            var paginationData = new PaginationData<DataTable>();
            var normalSql = "select t.*, rownum as rowno from v_user t where " + request.ToSql();
            var sql = request.Pagination.Enabled
                          ? string.Format("select * from ({0}) A where A.rowno > {1} and A.rowno <= {2}", normalSql,
                                          GetPageStart(request.Pagination), GetPageEnd(request.Pagination))
                          : normalSql;
            paginationData.Data = ExecuteDataTable(sql);
            if (request.Pagination.Enabled)
            {
                paginationData.RecordCount = ExecuteCount(normalSql.Replace("t.*, rownum as rowno", "count(*)"));
            }
            return paginationData;
        }

        /// <summary>
        /// 根据Id取单位数据。
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override DataTable GetData(string id)
        {
            var request = new Request();
            request.Items.Add(new Condition
            {
                FieldName = "USER_ID",
                Operator = ConditionOperator.Equal,
                Values = new object[] { id }
            });
            return GetData(request).Data;
        }

        /// <summary>
        /// 取表结构。
        /// </summary>
        /// <returns></returns>
        public override DataTable GetNoneData()
        {
            var request = new Request();
            request.Items.Add(new Condition
            {
                FieldName = "USER_ID",
                Operator = ConditionOperator.Equal,
                Values = new object[] { "null" }
            });
            return GetData(request).Data;
        }

        /// <summary>
        /// 新增用户。
        /// </summary>
        /// <param name="data"></param>
        public override void Add(DataTable data)
        {
            if (data.Rows.Count == 0)
            {
                return;
            }
            const string sql = "insert into USERTABLE(USER_ID,USERNAME,USERCODE,USERJB_ID,USERJBNAME,GLBM_ID,GLBMNAME,FZJG_ID,FZJGNAME,USERPASSWORD,USERROLE_ID)" +
                               "values(:USER_ID,:USERNAME,:USERCODE,:USERJB_ID,:USERJBNAME,:GLBM_ID,:GLBMNAME,:FZJG_ID,:FZJGNAME,:USERPASSWORD,:USERROLE_ID)";
            var parameters = new[]{
                                    MakeParam(":USER_ID", OracleType.VarChar, 40, "USER_ID"),
                                     MakeParam(":USERNAME", OracleType.VarChar, 40, "USERNAME"),
                                     MakeParam(":USERCODE", OracleType.VarChar, 40, "USERCODE"),
                                     MakeParam(":USERJB_ID", OracleType.VarChar, 40, "USERJB_ID"),
                                     MakeParam(":USERJBNAME", OracleType.VarChar, 40, "USERJBNAME"),
                                     MakeParam(":GLBM_ID", OracleType.VarChar, 40, "GLBM_ID"),
                                     MakeParam(":GLBMNAME", OracleType.VarChar, 40, "GLBMNAME"),
                                     MakeParam(":FZJG_ID", OracleType.VarChar, 40, "FZJG_ID"),
                                     MakeParam(":FZJGNAME", OracleType.VarChar, 40, "FZJGNAME"),
                                     MakeParam(":USERPASSWORD", OracleType.VarChar, 40, "USERPASSWORD"),
                                     MakeParam(":USERROLE_ID", OracleType.VarChar, 40, "USERROLE_ID")
                                  };
            OnAdd(data, sql, parameters);
        }

        /// <summary>
        /// 修改用户。
        /// </summary>
        /// <param name="data"></param>
        public override void Update(DataTable data)
        {
            if (data.Rows.Count == 0)
            {
                return;
            }
            const string sql = "update USERTABLE set " +
                               "USERNAME =:USERNAME" +
                               ",USERCODE=:USERCODE" +
                               ",USERJB_ID=:USERJB_ID" +
                               ",USERJBNAME=:USERJBNAME" +
                               ",GLBM_ID=:GLBM_ID" +
                               ",GLBMNAME=:GLBMNAME" +
                               ",FZJG_ID=:FZJG_ID" +
                               ",FZJGNAME=:FZJGNAME" +
                               ",USERPASSWORD=:USERPASSWORD" +
                               ",USERROLE_ID=:USERROLE_ID where USER_ID =:USER_ID";
            var parameters = new[]{
                                     MakeParam(":USERNAME", OracleType.VarChar, 40, "USERNAME"),
                                     MakeParam(":USERCODE", OracleType.VarChar, 40, "USERCODE"),
                                     MakeParam(":USERJB_ID", OracleType.VarChar, 40, "USERJB_ID"),
                                     MakeParam(":USERJBNAME", OracleType.VarChar, 40, "USERJBNAME"),
                                     MakeParam(":GLBM_ID", OracleType.VarChar, 40, "GLBM_ID"),
                                     MakeParam(":GLBMNAME", OracleType.VarChar, 40, "GLBMNAME"),
                                     MakeParam(":FZJG_ID", OracleType.VarChar, 40, "FZJG_ID"),
                                     MakeParam(":FZJGNAME", OracleType.VarChar, 40, "FZJGNAME"),
                                     MakeParam(":USERPASSWORD", OracleType.VarChar, 40, "USERPASSWORD"),
                                     MakeParam(":USERROLE_ID", OracleType.VarChar, 40, "USERROLE_ID"),
                                     MakeParam(":USER_ID", OracleType.VarChar, 40, "USER_ID")
                                 };
            OnUpdate(data, sql, parameters);
        }

        /// <summary>
        /// 初始化密码。
        /// </summary>
        /// <param name="userId"></param>
        public void ResetPassword(string userId)
        {
            const string sql = "update USERTABLE set USERPASSWORD = '{0}' where USER_ID = '{1}'";
            ExecuteNoneQuery(string.Format(sql, Md5.Encrypt("888888"), userId));
        }

        /// <summary>
        /// 删除用户数据。
        /// </summary>
        /// <param name="ids"></param>
        public override void Delete(List<string> ids)
        {
            const string sql = "delete from USERTABLE t where t.user_id in ('{0}')";
            ExecuteNoneQuery(string.Format(sql, string.Join("','", ids)));
        }

        /// <summary>
        /// 检查惟一性。
        /// </summary>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public bool IsUnique(string userCode)
        {
            var count = ExecuteCount(string.Format("select count(*) from USERTABLE where USERCODE = '{0}'", userCode));
            return count == 0;
        }
    }
}